﻿using System.Collections.Generic;
using System.Data.SqlClient;

namespace Central
{
    public class DataHandler
    {
        private readonly SqlConnection _sqlc;

        public DataHandler()
        {
            #region Hidden
            _sqlc = new SqlConnection("Server=ealdb1.eal.local;Database=EJL07_DB;User=ejl07_usr;Password=Baz1nga7");
            #endregion
            _sqlc.Open();
        }

        public void Close()
        {
            _sqlc.Close();
        }

        public List<PumpStation> GetPumpStations()
        {
            var stations = new List<PumpStation>();
            var stmt = new SqlCommand("SELECT id, name, ipaddress, port FROM PumpStations ORDER BY name ASC", _sqlc);
            using (var reader = stmt.ExecuteReader())
            {
                while (reader.Read())
                {
                    stations.Add(new PumpStation((int) reader["id"], (string) reader["name"], (string) reader["ipaddress"], (int) reader["port"]));
                }
            }
            return stations;
        } 

        public PumpStation CreatePumpStation(string name, string ipAddress, int port)
        {
            var stmt = new SqlCommand("INSERT INTO PumpStations (name, ipaddress, port) OUTPUT INSERTED.Id VALUES (@name, @ipaddress, @port);", _sqlc);
            
            stmt.Parameters.AddWithValue("@name", name);
            stmt.Parameters.AddWithValue("@ipaddress", ipAddress);
            stmt.Parameters.AddWithValue("@port", port);

            var id = (int) stmt.ExecuteScalar();

            return new PumpStation(id, name, ipAddress, port);
        }

        /*public void UpdatePumpStation(PumpStation station)
        {
            var stmt = new SqlCommand("UPDATE PumpStations SET name = @name, ipaddress = @ipaddress, port = @port WHERE id = @id", _sqlc);

            stmt.Parameters.AddWithValue("@name", station.Name);
            stmt.Parameters.AddWithValue("@ipaddress", station.IPAddress);
            stmt.Parameters.AddWithValue("@port", station.Port);
            stmt.Parameters.AddWithValue("@id", station.Id);

            stmt.ExecuteScalar();
        }*/

        public void DeletePumpStation(PumpStation station)
        {
            var stmt = new SqlCommand("DELETE FROM PumpStations WHERE id = @id", _sqlc);

            stmt.Parameters.AddWithValue("@id", station.Id);

            stmt.ExecuteScalar();
        }
    }
}
